SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- tblClubs'

PRINT '-- Column titles'
INSERT tblUFGTableTitles (utt_Tablename, utt_Languagename, utt_Title, utt_Description)
VALUES ('tblClubs','Nederlands','Clubs','Club gegevens')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_Name','Nederlands','Clubnaam','U dient een herkenbare Clubnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_longname','Nederlands','Volledige clubnaam','U dient een herkenbare volledige naam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_adres','Nederlands','Adres','U dient een adres in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_zipcode','Nederlands','Postcode','U dient een postcode in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_city','Nederlands','Plaats','U dient een plaats in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_telephone','Nederlands','Telefoonnummer','U dient een telefoonnummer in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_Email','Nederlands','Emailadres','U dient een emailadres in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_url','Nederlands','website','U dient een website in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_description','Nederlands','Tenue','U kunt hier een beschrijving in vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblclubs','clb_province','Nederlands','Provincie','U dient een provincie in te vullen.')


PRINT '-- Panels'
UPDATE tblUFGPanelTitles
SET upt_PanelTitle = 'Algemene clubgegevens'
WHERE upt_TableName = 'tblClubs'
AND upt_PanelNumber = 1
AND upt_Language= 'Nederlands' 

INSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan)
VALUES ('tblclubs',1,1,3,1)
iNSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan)
VALUES ('tblclubs',2,2,3,1)

UPDATE tblUFGColumns 
SET ucl_PanelNumber = 1 
WHERE ucl_TableName = 'tblClubs' 
AND ucl_ColumnName NOT IN ('clb_Description')


PRINT '-- Display'
UPDATE tblUFGTables 
SET utb_DisplayInCategory = 'MAINTABLES' 
WHERE utb_TableName = 'tblClubs' 

PRINT '-- Stored Procedures'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].sptblClubsUPDATE') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE sptblClubsUPDATE
GO

GO
CREATE PROCEDURE sptblClubsUPDATE
@UFGOld_clb_Name varchar(20),

@clb_Name varchar(20),
@clb_Longname varchar(80),
@clb_Adres varchar(50),
@clb_Zipcode varchar(7),
@clb_City varchar(20),
@clb_Province varchar(30),
@clb_Telephone varchar(20),
@clb_Email varchar(40),
@clb_URL varchar(40),
@clb_Description varchar(MAX),
@UFGUsername VARCHAR(200),
@UFGHostname VARCHAR(200)
AS
BEGIN
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN sptblClubsUPDATE
	ELSE
		BEGIN TRAN
    
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    -- REPEATABLE READ is noodzakelijk, omdat 
    -- check constraint tijdelijk worden uitgeschakeld.
    -- Een andere transactie zou hierdoor de kans krijgen om wedstrijden te wijzigen zonder bestaande clubs
    BEGIN TRY
	
	IF @UFGOld_clb_Name <> @clb_Name 
	BEGIN
		ALTER TABLE tblMatches NOCHECK CONSTRAINT FK_mat_AgainstClubname
		ALTER TABLE tblMatches NOCHECK CONSTRAINT FK_mat_Clubname

		UPDATE tblClubs SET
		clb_Name		= @clb_Name,  
		clb_Longname	= @clb_Longname,
		clb_Adres		= @clb_Adres,
		clb_Zipcode		= @clb_Zipcode,
		clb_City		= @clb_City,
		clb_Province	= @clb_Province,
		clb_Telephone	= @clb_Telephone,
		clb_Email		= @clb_Email,
		clb_URL			= @clb_URL,
		clb_Description = @clb_Description,		
		_Timestamp = GETDATE(), _Username = @UFGUsername, _Hostname = @UFGHostname
		WHERE clb_Name = @UFGOld_clb_Name
		
		UPDATE tblMatches SET 
		mat_AgainstClubname = @clb_Name,
		_Timestamp = GETDATE(), _Username = @UFGUsername, _Hostname = @UFGHostname
		WHERE mat_AgainstClubname = @UFGOld_clb_Name 

		UPDATE tblMatches SET 
		mat_Clubname = @clb_Name,
		_Timestamp = GETDATE(), _Username = @UFGUsername, _Hostname = @UFGHostname
		WHERE mat_Clubname = @UFGOld_clb_Name 

		ALTER TABLE tblMatches WITH CHECK CHECK CONSTRAINT FK_mat_Clubname
		ALTER TABLE tblMatches WITH CHECK CHECK CONSTRAINT FK_mat_AgainstClubname

	END	
	ELSE 
		UPDATE tblClubs SET 
		clb_Longname	= @clb_Longname,
		clb_Adres		= @clb_Adres,
		clb_Zipcode		= @clb_Zipcode,
		clb_City		= @clb_City,
		clb_Province	= @clb_Province,
		clb_Telephone	= @clb_Telephone,
		clb_Email		= @clb_Email,
		clb_URL			= @clb_URL,
		clb_Description = @clb_Description,		
		_Timestamp = GETDATE(), _Username = @UFGUsername, _Hostname = @UFGHostname
		WHERE clb_Name = @UFGOld_clb_Name


	IF @TranCount = 0
		COMMIT TRAN	
		
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	    
	    IF @TranCount > 0
			ROLLBACK TRAN sptblClubsUPDATE
		ELSE
			ROLLBACK TRAN
	END CATCH;
	IF @@TRANCOUNT > 0
	    COMMIT TRANSACTION;
END
GO

UPDATE tblUFGTables SET utb_Updatesp = 'sptblClubsUPDATE' 
WHERE utb_TableName = 'tblClubs'

PRINT '-- Row Rights'
INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLInsertExpression,urr_SQLUpdateExpression,urr_SQLDeleteExpression)
VALUES
('tblClubs','Voorzitter Club',
NULL,
'EXISTS(SELECT * 
		FROM tblChairmen 
		INNER JOIN tblPersons ON prs_ID = chm_PersonID 
		WHERE prs_Username = @UFGUsername
		AND chm_ClubName = @clb_Name)',
NULL)

INSERT tblUFGRowRightTitles
(urt_TableName,urt_RoleName,urt_Language,urt_InsertDescription,urt_UpdateDescription,urt_DeleteDescription)
VALUES ('tblClubs','Voorzitter Club','Nederlands',
NULL,
'Voorzitters mogen hun eigen club wijzigen',
NULL)